<?php
/*
SJK means ShuJuKu,which is a set of database classes.

@Author surfchen@gmail.com


Example #1:
$sjk=new SJK('mysql://root:pass@localhost/test');
$sjk->initSql('show tables');
$rs=$sjk->query();
$rs->fetchAll();
//OR while ($row=$rs->fetch()) {}

Example #2:
$post_id=5;
$sjk=new SJK('mysql://root:pass@localhost/wordpress');
$sjk->initSql('select * from wp_posts where ID=#1 limit 1');
$sjk->setInt(1,$post_id);
$rs=$sjk->query();
if ($arr=$rs->fetch()) {
    echo $arr['post_content'];
} else {
    echo 'post id:',$post_id,' not exists.';
}

Example #3:
$sjk=new SJK('mysql://root:pass@localhost/wordpress');
$sjk->initSql('select * from wp_users where user_login=#1 limit 1');
$sjk->setString(1,$name);
$rs=$sjk->query();
if ($arr=$rs->fetch()) {
    echo $arr['user_email'];
} else {
    echo 'user ',$name,' not exists.';
}

Example #4:
$update_arr=array('f1'=>'uuuu','f2'=>'dddd');
$sjk->initSql('UPDATE t1 SET #1 WHERE id=#2');
$sjk->setUpdateArray(1,$update_arr);
$sjk->setInt(2,3373);
$sjk->query();

Example #5:
insert_arr=array('f1'=>'pppp','f2'=>'iiii');
$sjk->initSql('INSERT INTO t1 #1');
$sjk->setInsertArray(1,$insert_arr);
$sjk->query();
*/
class SJK {
    protected $_link = NULL;
    protected $_host = NULL;
    private $_user = NULL;
    private $_pass = NULL;
    private $_database = NULL; 
    public  $mCharsetConnection='binary';
    public  $mCharsetClient='binary';
    public  $mCharsetResults='binary';

    private $_hash = '';
    public function __construct($host,$user,$pass,$database) {
        $this->_host = $host;
        $this->_user = $user;
        $this->_pass = $pass;
        $this->_database = $database;
        $this->_hash='#'.md5(rand(0,1000).time());
    }
    public function initSql($sql) {
        $this->mSql = preg_replace('/#([0-9]{1,3})/','#'.$this->_hash.'$1',$sql);
    }
    public function appendSql($sql) {
        $this->mSql .= ' '.preg_replace('/#([0-9]{1,3})/','#'.$this->_hash.'$1',$sql);
    }
    public function setInt($no,$val) {
        if (false===$this->_checkSqlNo($no)) {
            throw new SJKException('Invalid Sql No',1);
        }
        if (!preg_match('/^(:?[-+])?[0-9]+$/',$val)) {
            throw new SJKException('Invalid Int Input',1);
        }
        $this->mSql = str_replace('#'.$this->_hash.$no,$val,$this->mSql);
    }
    public function setInts($no,$vals) {
        if (false===$this->_checkSqlNo($no)) {
            throw new SJKException('Invalid Sql No',1);
        }
        if (!is_array($vals) || count($vals)<=0) {
            throw new SJKException('Invalid Int Array Input',1);
        }
        foreach ($vals as $val) {
            if (!preg_match('/^[0-9]+$/',$val)) {
                throw new SJKException('Invalid Input',1);
                return false;
            }
        }
        $this->mSql = str_replace('#'.$this->_hash.$no,'('.implode(',',$vals).')',$this->mSql);
    }
    public function setString($no,$val) {
        if (false===$this->_checkSqlNo($no)) {
            throw new SJKException('Invalid Sql No',1);
        }
        $this->mSql = str_replace('#'.$this->_hash.$no,"'".$this->escape($val)."'",$this->mSql);
    }
    public function setStrings($no,$vals) {
        if (false===$this->_checkSqlNo($no)) {
            throw new SJKException('Invalid Sql No',1);
        }
        foreach ($vals as $k => $val) {
            $vals[$k]=$this->escape($val);
        }
        $this->mSql = str_replace('#'.$this->_hash.$no,"('".implode("','",$vals)."')",$this->mSql);
    }
    public function setInsertArray($no,$arr) {
        if (!is_array($arr)) {
            throw new Exception('SJK_INSERT_ARRAY_SHOULD_BE_ARRAY');
        }
        if (count($arr)<=0) {
            throw new Exception('SJK_INSERT_ARRAY_ZERO');
        }
        $field=$value='';
        foreach ($arr as $k => $v) {
            if (!$this->isValidField($k)) {
                throw new Exception('SJK_INVALID_FIELD_NAME');
            }
            $field.=",`".$k."`";
            $value.=",'".$this->escape($v)."'";
        }
        $field=substr($field,1);
        $value=substr($value,1);
        $insert="($field) values ($value)";
        $this->mSql = str_replace('#'.$this->_hash.$no,$insert,$this->mSql);
    }
    public function setUpdateArray($no,$arr) {
        if (!is_array($arr)) {
            throw new Exception('SJK_UPDATE_ARRAY_SHOULD_BE_ARRAY');
        }
        if (count($arr)<=0) {
            throw new Exception('SJK_UPDATE_ARRAY_ZERO');
        }
        $set='';
        foreach ($arr as $k => $v) {
            if (!$this->isValidField($k)) {
                throw new Exception('SJK_INVALID_FIELD_NAME');
            }
            $set.=',`'.$k.'`'."='".$this->escape($v)."'";
        }
        $set=substr($set,1);
        $this->mSql = str_replace('#'.$this->_hash.$no,$set,$this->mSql);
    }
    public function query($try_reconnect=true) {
        $this->connect(!$try_reconnect);
        if ($GLOBALS['sjk_pre_db']!=$this->_database) {
            $GLOBALS['sjk_pre_db']=$this->_database;
            mysql_select_db($this->_database,$this->_link);
        }
        //把hash后的#x替换回原始的
        //原因: 他们之所以没有被set，有可能是因为本来就是这样
        if (strpos($this->mSql, '#'.$this->_hash)!==false) {
            $this->mSql = str_replace('#'.$this->_hash,'#',$this->mSql);
        }
        $r=mysql_query($this->mSql,$this->_link);
        if (is_bool($r)) {
            if ($r==false) {
                if ($this->errno()==2006 && $try_reconnect==true) {
                    $this->_link=false;
                    return $this->query(false); 
                }
                throw new SJKException($this->error(),$this->errno());
            }
            return $r;
        } else {
            return new SJKResult($r);
        }
    }
    public function getAllFromTable($table) {
        $this->initSql('SELECT * FROM '.$table);
        $rs=$this->query();
        $arr=array();
        while ($row=$rs->fetch()) {
            $arr[]=$row;
        }
        return $arr;
    }

    public function getInsertId() {
        $this->initSql('SELECT LAST_INSERT_ID() as lid');
        $rs=$this->query();
        if ($row=$rs->fetch()) {
            return $row['lid'];
        }
        return mysql_insert_id($this->_link);
    }

    protected function connect($real=false) {
        if ($this->_link) {
            return true;
        }
        $this->_link = mysql_connect($this->_host,$this->_user,$this->_pass,$real,MYSQL_CLIENT_COMPRESS);
        if (!$this->_link) {
            throw new SJKException('Fail to connect database',1);
        }
        mysql_query('SET character_set_connection='.$this->mCharsetConnection.
        ',character_set_results='.$this->mCharsetResults.
        ',character_set_client='.$this->mCharsetClient);
        $GLOBALS['sjk_pre_db']=$this->_database;
        return mysql_select_db($this->_database,$this->_link);
    }
    public function escape($str) {
        $this->connect();
        $encoded=mysql_real_escape_string($str,$this->_link);
        if ($this->errno()==2006) {
            $this->_link=false;
            $this->connect(true);
            $encoded=mysql_real_escape_string($str,$this->_link);
        }
        return $encoded;
    }
    public function affectedRows() {
        return mysql_affected_rows($this->_link);
    }
    public function error() {
        return mysql_error($this->_link);
    }
    public function errno() {
        return mysql_errno($this->_link);
    }
    private function _checkSqlNo($no) {
        return (bool)preg_match('/^[0-9]{1,3}$/',$no);
    }
    private function isValidField($name) {
        return (bool)preg_match('/^[a-zA-Z0-9_]+$/',$name);
    }
}
class SJKResult implements Iterator {
    private $_result;
    private $offset=0;
    public function __construct($result) {
        $this->_result=$result;
    }
    public function fetch($mode=MYSQL_ASSOC) {
        return mysql_fetch_array($this->_result,$mode);
    }
    public function fetchAll($mode=MYSQL_ASSOC) {
        $arr = array();
        while ($row=$this->fetch($mode)) {
            $arr[]=$row;
        }
        return $arr;
    }
    public function first() {
    }
    public function num() {
        return mysql_num_rows($this->_result);
    }
    public function current() {
        return mysql_fetch_array($this->_result,MYSQL_ASSOC);
    }
    public function key() {
        return $this->offset;
    }
    public function next() {
        $this->offset++;
    }
    public function rewind() {
        if ($this->offset>0) {
            mysql_data_seek($this->_result,0);
            $this->offset=0;
        }
    }
    public function valid() {
        return $this->offset<$this->num();
    }
    public function __destruct() {
        unset($this->_result);
    }
}
class SJKException extends Exception {
}
